package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.CorpPublicationPage;

@Repository
public class CorpPublicationPageDao  {

	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	private static String tableName="Corp_PublicationPage";
	
	
	private static String QUERY_ALL = "SELECT id,thumbPath,ipagePath,createDate,modDate," +
			"isValid,publicationId,orderby,pubpageSize,isad FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"id,thumbPath,ipagePath,createDate,modDate," +
			"isValid,publicationId,orderby,pubpageSize,isad)VALUES(" +
			":id,:thumbPath,:ipagePath,:createDate,:modDate," +
			":isValid,:publicationId,:orderby,:pubpageSize,:isad)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE id=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName + " SET " +
			"thumbPath=:thumbPath,ipagePath=:ipagePath,createDate=:createDate,modDate=:modDate," +
			"isValid=:isValid,publicationId=:publicationId,orderby=:orderby,pubpageSize=:pubpageSize,isad=:isad"+
			"WHERE id=:id";

	private final ParameterizedRowMapper<CorpPublicationPage> mapper = new ParameterizedRowMapper<CorpPublicationPage>() {
		public CorpPublicationPage mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpPublicationPage obj = new CorpPublicationPage();
			obj.setId(rs.getLong(("id")));
			obj.setThumbPath(rs.getString("thumbPath"));
			obj.setIpagePath(rs.getString("ipagePath"));
			obj.setCreateDate(rs.getTimestamp("createDate"));
			obj.setModDate(rs.getTimestamp("modDate"));
			obj.setIsValid(rs.getInt("isValid"));
			obj.setPublicationId(rs.getInt("publicationId"));
			obj.setOrderby(rs.getInt("orderby"));
			obj.setPubpageSize(rs.getInt("pubpageSize"));
			obj.setIsad(rs.getInt("isad"));
			return obj;
		}
	};

	public List<CorpPublicationPage> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpPublicationPage findById(Long id) {
		List<CorpPublicationPage> list = template.query(QUERY_ALL + " WHERE functionId=?",
				new Object[] { id }, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}


	
	public void insert(CorpPublicationPage bean) {
		if(bean.getId()==null){
			bean.setId(new Long(appSeqDao.getSequence("corp_publicationpage_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}
	
	public boolean add(CorpPublicationPage bean) {
		boolean flag=false;
		try{
			SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
			NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
					template.getDataSource());
			npTemplate.update(INSERT_SQL, ps);
			flag=true;
		}catch(Exception e){	
		}
		
		return flag;
	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public boolean isDeletePublicationpage(int publicationpageid) {
		boolean isDelete = false;
		try {
			String sql = "update Publicationpage set isvalid=? where publicationpageid=?";
			Object[] params = new Object[] { 2, publicationpageid };
			template.update(sql, params);
			isDelete = true;
		} catch (Exception e) {			
		}
		return isDelete;
	}


	public void update(CorpPublicationPage bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public int getOrderbyMax(int pubId){
		int maxorderby=0;
		List list = template.query(QUERY_ALL+"  where publicationId =?  and  isValid=? order by orderby desc",
				new Object[] { pubId, 0}, mapper);
		if (list.size() < 1) {
			return maxorderby;
		}
		CorpPublicationPage publicationpage= (CorpPublicationPage)list.get(0);
		maxorderby=publicationpage.getOrderby();
		return maxorderby;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	public List<CorpPublicationPage> findByPubId(int pubId){
		List<CorpPublicationPage> lists = template.query(QUERY_ALL + " where publicationId =?  and  isValid=? ",
				new Object[] { pubId,1}, mapper);
		return lists;
	}
}
